﻿using System.Linq;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Linq.Test
{
    /// <summary>
    /// A set of tests for JulaDB LINQ provider. They test whether LINQ queries were properly translated to SQL,
    /// executed and whether they returned valid results. All queries in this test class are based on a single table.
    /// </summary>
    [TestClass]
    public class SelectTest
    {
        private static JulaDbConnection _connection;
        private static QueryableSources _sources;
        private static PersonTestDataCreator _peopleData;
        private static CustomerOrderTestDataCreator _customerData;

        [ClassInitialize]
        public static void InitializeDatabase(TestContext context)
        {
            _connection = TestUtils.CreateTestDatabase();
            _peopleData = new PersonTestDataCreator(_connection);
            _customerData = new CustomerOrderTestDataCreator(_connection);
            _sources = new QueryableSources(_connection);
        }
        
        [ClassCleanup]
        public static void CleanupDatabase()
        {
            _connection.Close();
        }

        /// <summary>
        /// A simplest LINQ query getting all objects from the source.
        /// </summary>
        [TestMethod]
        public void SelectAll()
        {
            var query = from p in _sources.Persons select p;
            var list = query.ToList();
            Assert.AreEqual(_peopleData.TotalPersonCount, list.Count);
        }

        /// <summary>
        /// A LINQ query creating a collection of anonymous objects.
        /// </summary>
        [TestMethod]
        public void SelectAnonymous()
        {
            var query = from p in _sources.Persons
                        select new { Name = p.FirstName + " " + p.LastName, Age = p.Age };
            var list = query.ToList();
            Assert.AreEqual(_peopleData.TotalPersonCount, list.Count);
        }

        /// <summary>
        /// A LINQ query with a WHERE condition checking that a value in within a given range.
        /// </summary>
        [TestMethod]
        public void SelectWhere()
        {
            var query = from p in _sources.Persons
                        where p.Age > 20 && p.Age < 60
                        select p;
            var list = query.ToList();
            Assert.AreEqual(3, list.Count);
        }

        /// <summary>
        /// Expression used to construct property value should be translated to SQL.
        /// </summary>
        [TestMethod]
        public void SelectRowExpression()
        {
            var query = from p in _sources.Persons
                        select new { FirstName = p.FirstName, Months = 12 * p.Age };
            var list = query.ToList();

            Assert.AreEqual(_peopleData.TotalPersonCount, list.Count);
            Assert.AreEqual(12.4M * 12, list.First().Months);
        }

        /// <summary>
        /// Selecting values from a single column.
        /// </summary>
        [TestMethod]
        public void SelectColumn()
        {
            var query = from p in _sources.Persons select p.Age;
            var list = query.ToList();
            Assert.AreEqual(_peopleData.TotalPersonCount, list.Count);
            Assert.AreEqual(_peopleData.MaxAge, list.Max());
            Assert.AreEqual(_peopleData.MinAge, list.Min());
        }

        /// <summary>
        /// A test for translation of LINQ to aggregate function sum().
        /// </summary>
        [TestMethod]
        public void SelectCount()
        {
            var result = (from p in _sources.Persons select p.Age).Count();
            Assert.AreEqual(_peopleData.TotalPersonCount, result);
        }

        /// <summary>
        /// A test for translation of LINQ to aggregate function avg().
        /// </summary>
        [TestMethod]
        public void SelectAverage()
        {
            var result = (from p in _sources.Persons select p.Age).Average();
            Assert.AreEqual(_peopleData.AverageAge, result);
        }

        /// <summary>
        /// A test for translation of LINQ to aggregate function sum().
        /// </summary>
        [TestMethod]
        public void SelectSum()
        {
            var result = (from p in _sources.Persons select p.Age).Sum();
            Assert.AreEqual(_peopleData.SumAge, result);
        }

        /// <summary>
        /// A test for translation of LINQ to aggregate function min().
        /// </summary>
        [TestMethod]
        public void SelectMin()
        {
            var result = (from p in _sources.Persons select p.Age).Min();
            Assert.AreEqual(_peopleData.MinAge, result);
        }

        /// <summary>
        /// A test for translation of LINQ to aggregate function max().
        /// </summary>
        [TestMethod]
        public void SelectMax()
        {
            var result = (from p in _sources.Persons select p.Age).Max();
            Assert.AreEqual(_peopleData.MaxAge, result);
        }

        /// <summary>
        /// Executing max() on an empty collection.
        /// </summary>
        [TestMethod]
        public void SelectMaxNoValues()
        {
            var result = (from p in _sources.Persons where p.Age > p.Age select p.Age).Max();
            Assert.AreEqual(null, result);
        }

        /* LINQ provider currently does not support grouping
        [TestMethod]
        public void SelectAverageAgeByLastName()
        {
            var result = from p in _sources.Persons
                         group p by p.LastName into g
                         select new { LastName = g.Key, AverageAge = g.Average(i => i.Age) };
            var list = result.ToList();
        }*/

        /// <summary>
        /// Test for translating LINQ to ORDER BY asc.
        /// </summary>
        [TestMethod]
        public void SelectOrderByAsc()
        {
            var result = from p in _sources.Persons
                         orderby p.Age ascending
                         select p;
            var list = result.ToList();

            Assert.AreEqual(_peopleData.TotalPersonCount, list.Count);
            Assert.AreEqual(_peopleData.MinAge, list.First().Age);
        }

        /// <summary>
        /// Test for translating LINQ to ORDER BY desc.
        /// </summary>
        [TestMethod]
        public void SelectOrderByDesc()
        {
            var result = from p in _sources.Persons
                         where p.Age != null
                         orderby p.Age descending
                         select p;
            var list = result.ToList();

            Assert.AreEqual(_peopleData.AgeNotNullPersonCount, list.Count);
            Assert.AreEqual(_peopleData.MaxAge, list.First().Age);
        }

        /// <summary>
        /// A test for translation to SQL LIKE.
        /// </summary>
        [TestMethod]
        public void CustomerNameStartsWith()
        {
            var query = from c in _sources.Customers
                        where c.Name.StartsWith("J")
                        select c;
            var list = query.ToList();
            Assert.AreEqual(2, list.Count);
        }

        /// <summary>
        /// A test for translation to SQL LIKE.
        /// </summary>
        [TestMethod]
        public void CustomerNameEndsWith()
        {
            var query = from c in _sources.Customers
                        where c.Name.EndsWith("an")
                        select c;
            var list = query.ToList();
            Assert.AreEqual(1, list.Count);
        }

        /// <summary>
        /// A test for translation to SQL LIKE.
        /// </summary>
        [TestMethod]
        public void CustomerNameContains()
        {
            var query = from c in _sources.Customers
                        where c.Name.Contains("nn")
                        select c;
            var list = query.ToList();
            Assert.AreEqual(1, list.Count);
        }

        /// <summary>
        /// A test for translation to SQL LIKE.
        /// </summary>
        [TestMethod]
        public void CustomerNameNotContains()
        {
            var query = from c in _sources.Customers
                        where !c.Name.Contains("test")
                        select c;
            var list = query.ToList();
            Assert.AreEqual(_customerData.TotalPersonCount, list.Count);
        }

        /// <summary>
        /// Translating LINQ Distinct() to SQL DISTINCT.
        /// </summary>
        [TestMethod]
        public void CustomerNameUnique()
        {
            var query = (from c in _sources.Customers select c).Distinct();
            var list = query.ToList();
            Assert.AreEqual(_customerData.TotalPersonCount, list.Count);
        }

        /// <summary>
        /// Translation to SQL with min() and LIKE.
        /// </summary>
        [TestMethod]
        public void SelectMinWithCondition()
        {
            var result = (from p in _sources.Customers
                          where p.Name.StartsWith("Ma")
                          select p.CustomerId).
                          Min();
            Assert.AreEqual(1, result);
        }

        /// <summary>
        /// A query with ordering by two columns and a DISTINCT.
        /// </summary>
        [TestMethod]
        public void SelectDistinctOrderBy()
        {
            var query = (from p in _sources.Customers
                          orderby p.Name descending, p.CustomerId ascending
                          select p).
                          Distinct();
            var list = query.ToList();
            Assert.AreEqual(_customerData.TotalPersonCount, list.Count);
        }
    }
}
